//群体运营
//随机找到一个地理圈id
MATCH (n:LAC) WHERE n.group_geo_id IS NOT NULL
WITH n.group_geo_id AS comid, rand() AS r ORDER BY r LIMIT 1

// 地理圈画像获取
OPTIONAL MATCH (n:LAC {group_geo_id: comid})
OPTIONAL MATCH (u:User {group_geo_id: comid})
OPTIONAL MATCH (p:Phone {group_geo_id: comid})

WITH comid, {
  lac_count: count(distinct n),
  user_count: count(distinct u),
  avg_monthly_fee: avg(p.monthly_fee_rmb),
  avg_free_call_minutes: avg(p.free_call_minutes),
  avg_data_allowance_gb: avg(p.data_allowance_gb),
  group_avg_daily_traffic_mb: avg(p.avg_daily_traffic_mb),
  group_avg_domestic_daily_call_min: avg(p.avg_domestic_daily_call_min)
} AS profile

// APP 使用特征：先聚合再 collect，避免“聚合嵌套”
OPTIONAL MATCH (p:Phone {group_geo_id: comid})-[r:use_app]->(end:APP)
WHERE end.app_name IS NOT NULL AND r.online_duration_minutes IS NOT NULL
WITH comid, profile, end.app_name AS appName,
     sum(r.online_duration_minutes) AS totalDuration,
     count(r) AS connectionCount
WITH comid, profile,
     collect({appName: appName, totalDuration: totalDuration, connectionCount: connectionCount}) AS appUsage
WITH comid, apoc.map.merge(profile, {appUsage: appUsage}) AS profile
// 地理圈画像获取结束

//获取地理圈详情的节点和边
MATCH group = (g1:User|LAC{group_geo_id: comid})-[gr*1..2]->(g2 {group_geo_id: comid})
WITH nodes(group) as nodes, relationships(group) as rels, comid, profile

//获取离群用户并返回时带上颜色前缀
OPTIONAL match (lowUser:User {group_geo_id: comid})-[:has]->(p:Phone {group_geo_id: comid}) where (
  p.monthly_fee_rmb < profile['avg_monthly_fee']*0.2 or
  p.free_call_minutes < profile['avg_free_call_minutes']*0.2 or
  p.data_allowance_gb < profile['avg_data_allowance_gb']*0.2 or
  p.avg_daily_traffic_mb < profile['group_avg_daily_traffic_mb']*0.2 or
  p.avg_domestic_daily_call_min < profile['group_avg_domestic_daily_call_min']*0.2
)
return nodes, rels, profile, collect(lowUser) as orange_lowUser